package com.yangye.pisystem.service.impl;

import com.yangye.picommon.utils.OssUtil;
import com.yangye.pisystem.service.ExcelService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.util.Arrays;
import java.util.List;

/**
 * @author yangye
 * @date 2019/8/29
 */
@Service
public class ExcelServiceImpl implements ExcelService {
	@Autowired
	private OssUtil ossUtil;

	@Override
	public String handleExcel(MultipartFile file) throws IOException {
		HSSFWorkbook workbook = new HSSFWorkbook(file.getInputStream());
		int activeSheetIndex = workbook.getActiveSheetIndex();
		HSSFSheet sheet = workbook.getSheetAt(activeSheetIndex);
		for (int rowNum = 3; rowNum < sheet.getLastRowNum(); rowNum++) {
			HSSFRow row = sheet.getRow(rowNum);
			HSSFCell cell = row.getCell(3);
			String value = cell.getStringCellValue();
			cell.setCellValue(this.sort(value));
		}

		// 将转换完成的excel写入输出流，再转成输入流
		ByteArrayOutputStream os = new ByteArrayOutputStream();
		workbook.write(os);
		byte[] bytes = os.toByteArray();
		// 上传至阿里云
		ByteArrayInputStream inputStream = new ByteArrayInputStream(bytes);

		String fileName = System.currentTimeMillis() + ".xls";
		String key = ossUtil.upload(inputStream, fileName);
		String downloadUrl = ossUtil.getDownloadUrl(key);
		return downloadUrl;
	}

	/**
	 * 重新排序
	 * 	a1,a2,a3,...,a100
	 * @param value
	 * @return
	 */
	private String sort(String value) {
		String[] arr = value.split(",");
		List<String> list = Arrays.asList(arr);
		list.sort((o1, o2) -> {
			String s1 = o1.replaceAll("\\d+", "");
			String s2 = o2.replaceAll("\\d+", "");
			Integer n1 = Integer.valueOf(o1.replaceAll("\\D+", ""));
			Integer n2 = Integer.valueOf(o2.replaceAll("\\D+", ""));
			return s1.compareTo(s2) == 0 ? (n1 - n2) : (s1.compareTo(s2));
		});
		return String.join(",", list);
	}
}
